package com.weimai.flower.app.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.alibaba.fastjson.JSONObject;

import com.weimai.flower.app.entity.FlowerNameEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

/**
 * Copyright (c) 2017 Choice, Inc.
 * All Rights Reserved.
 * Choice Proprietary and Confidential.
 *
 *
 * @author yunhao
 * @since 2019-07-26 15:55
 */
@Repository
public class FlowerNameDaoImpl implements FlowerNameDao {

    private static final Logger log = LoggerFactory.getLogger(FlowerNameDaoImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void save(FlowerNameEntity entity){

        try{
            jdbcTemplate.update("insert into flower(flower,spell,flag,create_time,update_time) "
                                + "values(?,?,?,?,?) ",entity.getFlowerName(),entity.getSpell(),entity.convertFlag(),entity.getCreateTime(),entity.getUpdateTime());
        }catch (Exception e){
            log.warn(" save error !   messsage:" + e.getMessage() + " data:" + JSONObject.toJSONString(entity));
        }

    }

    @Override
    public FlowerNameEntity getByName(String flowerName){
        List<FlowerNameEntity>  dataList  = jdbcTemplate.query(" select id,flower,spell,flag,create_time,update_time from flower where flower = ? limit 1 ",
                                                 new FlowerNameRowMapper(),flowerName);

        if(dataList!=null&&!dataList.isEmpty()){
            return dataList.get(0);
        }else{
            return null;
        }
    }

    @Override
    public List<FlowerNameEntity> getBySpell(String spell){
        return jdbcTemplate.query(" select id,flower,spell,flag,create_time,update_time from flower where spell = ?  ",
                                                               new FlowerNameRowMapper(),spell);
    }

    @Override
    public void delete(int id){
        jdbcTemplate.update(" delete from flower where id = ? ",id);
    }

    @Override
    public void batchUpdate(List<FlowerNameEntity> entityList){

        List<Object[]> saveList = new ArrayList<>();

        for (FlowerNameEntity entity : entityList) {
            saveList.add(new Object[]{entity.getFlowerName(),entity.getSpell(),entity.isUseFlag(),entity.getCreateTime(),entity.getUpdateTime()});
        }//end foreach

        jdbcTemplate.batchUpdate("insert into flower(flower,spell,flag,create_time,update_time) values(?,?,?,?,?)",saveList);
    }

    @Override
    public void setUsed(int id) {
        jdbcTemplate.update("update flower set flag = 1, update_time = ? where id = ? ",new Date(),id );
    }

    @Override
    public void setUnused(int id) {
        jdbcTemplate.update("update flower set flag = 0, update_time = ? where id = ? ",new Date(),id );
    }


    @Override
    public boolean isExist(String flowerName, String spell){
        long count = jdbcTemplate.queryForObject(" select count(*) from flower where (flower = ? or spell = ?) and flag = 1 ",Integer.class,flowerName,spell);
        return count>0;
    }

    @Override
    public List<FlowerNameEntity> getRandomList(int limit){
        String sql = " select id,flower,spell,flag,create_time,update_time from flower where flag = 0 order by rand() limit ?  ";
        return jdbcTemplate.query(sql,new FlowerNameRowMapper(),  limit);
    }

    @Override
    public List<FlowerNameEntity> getList(String words,int useFlag, int pageSize, int currentPage){

        String sql = " select id,flower,spell,flag,create_time,update_time from flower ";

        List<Object> paramList = new ArrayList<>();

        if(useFlag==-1){
            sql += "where 1 = 1 ";
        }else{
            sql += "where flag = ? ";
            paramList.add(useFlag);
        }

        if(words!=null){
            sql += " and ( flower like ? or spell like ? )   ";
            paramList.add("%"+words+"%");
            paramList.add("%"+words+"%");
        }

        sql += " order by update_time desc limit ?,? ";
        paramList.add((currentPage-1)*pageSize);
        paramList.add(pageSize);

        return jdbcTemplate.query(sql,new FlowerNameRowMapper(),paramList.toArray());
    }

    @Override
    public int getCount(String words, int useFlag){

        String sql = " select count(*) from flower ";

        List<Object> paramList = new ArrayList<>();

        if(useFlag==-1){
            sql += "where 1 = 1 ";
        }else{
            sql += "where flag = ? ";
            paramList.add(useFlag);
        }

        if(words!=null){
            sql += " and ( flower like ? or spell like ? )   ";
            paramList.add("%"+words+"%");
            paramList.add("%"+words+"%");
        }

        return jdbcTemplate.queryForObject(sql,Integer.class,paramList.toArray());

    }


    @Override
    public List<FlowerNameEntity> getAll(int useFlag){
        String sql = " select id,flower,spell,flag,create_time,update_time from flower ";

        if(useFlag!=-1){
            sql += " where flag =  "+useFlag;
        }

        return jdbcTemplate.query(sql,new FlowerNameRowMapper());
    }

    class FlowerNameRowMapper implements RowMapper<FlowerNameEntity> {
        public FlowerNameEntity mapRow(ResultSet rs, int rowNum) throws SQLException {

            FlowerNameEntity entity = new FlowerNameEntity();
            entity.setId(rs.getInt("id"));
            entity.setFlowerName(rs.getString("flower"));
            entity.setSpell(rs.getString("spell"));
            int flag  = rs.getInt("flag");
            if(flag==1){
                entity.setUseFlag(true);
            }else{
                entity.setUseFlag(false);
            }
            entity.setCreateTime(rs.getTimestamp("create_time"));
            entity.setUpdateTime(rs.getTimestamp("update_time"));
            return entity;
        }

    }


}
